#!/usr/bin/env python3
"""
数据库迁移脚本 - 添加任务追踪字段
为Position表添加task_id、task_type、operator、operation_time字段
"""

import sqlite3
import os
import sys
from datetime import datetime
from pathlib import Path

# 添加项目根目录到Python路径
current_dir = Path(__file__).parent.parent
sys.path.insert(0, str(current_dir))

# 导入路径管理器
from src.core.path_manager import path_manager

def migrate_database():
    """执行数据库迁移"""
    # 使用路径管理器获取数据库路径
    db_path = path_manager.get_database_file()
    
    print(f"数据库路径: {db_path}")
    print(f"部署模式: {path_manager.deployment_mode}")
    
    if not db_path.exists():
        print("数据库文件不存在，无需迁移")
        return
    
    print("开始数据库迁移...")
    
    try:
        conn = sqlite3.connect(str(db_path))
        cursor = conn.cursor()
        
        # 检查是否已经存在新字段
        cursor.execute("PRAGMA table_info(positions)")
        columns = [column[1] for column in cursor.fetchall()]
        
        if 'task_id' in columns:
            print("数据库已经包含任务追踪字段，无需迁移")
            return
        
        print("正在添加任务追踪字段...")
        
        # 添加新字段（使用常量默认值）
        cursor.execute("ALTER TABLE positions ADD COLUMN task_id TEXT DEFAULT ''")
        cursor.execute("ALTER TABLE positions ADD COLUMN task_type TEXT DEFAULT ''")
        cursor.execute("ALTER TABLE positions ADD COLUMN operator TEXT DEFAULT ''")
        cursor.execute("ALTER TABLE positions ADD COLUMN operation_time TEXT DEFAULT ''")
        
        # 为现有数据设置默认值
        current_time = datetime.now().isoformat()
        cursor.execute("""
            UPDATE positions 
            SET task_id = '', 
                task_type = CASE 
                    WHEN sample_id = '#999' THEN 'disable'
                    WHEN sample_id != '' AND sample_id != '#999' THEN 'place'
                    ELSE 'init'
                END,
                operator = 'system',
                operation_time = ?
        """, (current_time,))
        
        conn.commit()
        print("✅ 数据库迁移完成")
        print(f"   - 添加了task_id字段")
        print(f"   - 添加了task_type字段")
        print(f"   - 添加了operator字段")
        print(f"   - 添加了operation_time字段")
        print(f"   - 更新了现有数据的默认值")
        
    except Exception as e:
        print(f"❌ 数据库迁移失败: {e}")
        conn.rollback()
    finally:
        conn.close()

if __name__ == "__main__":
    migrate_database() 